﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using Telerik.Web.UI;
using Aspose.Excel;

/// <summary>
/// Public static methods
/// </summary>
public class Utils
{
	public Utils()
	{
		
	}

  private static HttpContext contextCurrent
  {
    get
    {
      return System.Web.HttpContext.Current;
    }
  }

  public static Excel GetExcel()
  {
    License license = new License();
    license.SetLicense(contextCurrent.Server.MapPath("~/Aspose.Excel.lic"));
    Excel ex = new Excel();

    return ex;
  }

  /// <summary>
  /// Out data to Excel according to grid
  /// </summary>
  /// <param name="data">Data array</param>
  /// <param name="grid">Grid</param>
  /// <param name="ws">Excel worksheet</param>
  /// <param name="writeHeader">Flag: true - out header, false - no</param>
  /// <param name="startRow">First row number</param>
  public static void GridToExcel(DataTable data, RadGrid grid, Worksheet ws, bool writeHeader, ref int startRow)
  {
    DataTable dtOut = new DataTable();
    DataTable dtHeader = new DataTable();
    List<string> lstGridColumnName = new List<string>();

    DataTable dtOrder = new DataTable();
    dtOrder.Columns.Add("ColumnIndex", typeof(int));
    dtOrder.Columns.Add("OrderedIndex", typeof(int));

    for (int i = 0; i < grid.Columns.Count; ++i)
      dtOrder.LoadDataRow(new object[] { i, grid.Columns[i].OrderIndex }, true);

    DataView dvOrder = new DataView(dtOrder, "", "OrderedIndex", DataViewRowState.CurrentRows);
    for (int j = 0; j < dvOrder.Count; ++j)
    {
      int i = Convert.ToInt32(dvOrder[j]["ColumnIndex"]);

      if ((!grid.Columns[i].Display) || (!grid.Columns[i].Visible))
        continue;

      string dataField = "";

      if (grid.Columns[i] is GridBoundColumn)
        dataField = (grid.Columns[i] as GridBoundColumn).DataField;
      else if (grid.Columns[i] is GridTemplateColumn)
        dataField = (grid.Columns[i] as GridTemplateColumn).DataField;
      else if (grid.Columns[i] is GridHyperLinkColumn)
        dataField = (grid.Columns[i] as GridHyperLinkColumn).DataTextField;

      if (dataField == "")
        continue;
        
      dtOut.Columns.Add("Field" + i.ToString(), data.Columns[dataField].DataType);
      lstGridColumnName.Add(dataField);
      dtHeader.Columns.Add("Header" + i.ToString(), typeof(string));
    }

    foreach (DataRow dr in data.Rows)
    {
      DataRow drData = dtOut.NewRow();
      foreach (DataColumn dc in dtOut.Columns)
      {
        drData[dc.ColumnName] = dr[lstGridColumnName[dc.Ordinal]];
      }
      dtOut.Rows.Add(drData);
    }

    DataRow drHeader = dtHeader.NewRow();
    for (int i = 0; i < grid.Columns.Count; ++i)
    {
      if ((!grid.Columns[i].Display) || (!grid.Columns[i].Visible))
        continue;

      drHeader["Header" + i.ToString()] = grid.Columns[i].HeaderText;
    }
    dtHeader.Rows.Add(drHeader);

    if (writeHeader)
    {
      ws.Cells.ImportDataTable(dtHeader, false, startRow, 0);
      for (int i = 0; i < dtHeader.Columns.Count; ++i)
        ws.Cells[startRow, i].Style.Font.IsBold = true;
      ++startRow;
    }

    ws.Cells.ImportDataTable(dtOut, false, startRow, 0);
    startRow += dtOut.Rows.Count + 1;
  }

  /// <summary>
  /// Out data to Excel according to grid (by the template)
  /// </summary>
  /// <param name="data">Data array</param>
  /// <param name="grid">Grid</param>
  /// <param name="templateName">Template file name</param>
  /// <param name="startRow">First row number</param>
  public static void GridToExcel(DataTable data, RadGrid grid, string templateName, ref int startRow)
  {
    License license = new License();
    license.SetLicense(contextCurrent.Server.MapPath("~/Aspose.Excel.lic"));

    Excel ex = new Excel();
    ex.Open(contextCurrent.Server.MapPath(string.Format("~/ExcelTemplate/{0}.xls", templateName)));
    Worksheet ws = ex.Worksheets[0];

    GridToExcel(data, grid, ws, false, ref startRow);

    ex.Save(string.Format("{0}Download.xls", templateName), FileFormatType.Excel2003, SaveType.OpenInExcel, contextCurrent.Response);
    contextCurrent.Response.End(); 
  }

  /// <summary>
  /// Out data to Excel according to field list (by the template)
  /// </summary>
  /// <param name="data">Data array</param>
  /// <param name="listFields">List of fields</param>
  /// <param name="templateName">Template file name</param>
  /// <param name="showResult">Flag: true - show report, false - no</param>
  /// <param name="startRow">First row number</param>
  public static Excel GridToExcel(DataTable data, string[] listFields, string templateName, bool showResult, ref int startRow)
  {
    Excel ex = GetExcel();
    ex.Open(contextCurrent.Server.MapPath(string.Format("~/ExcelTemplate/{0}.xls", templateName)));
    Worksheet ws = ex.Worksheets[0];

    DataTable dtOut = new DataTable();
    for (int j = 0; j < listFields.Length; ++j)
      dtOut.Columns.Add("Field" + j.ToString(), data.Columns[listFields[j]].DataType);

    foreach (DataRow dr in data.Rows)
    {
      DataRow drData = dtOut.NewRow();
      foreach (DataColumn dc in dtOut.Columns)
      {
        drData[dc.ColumnName] = dr[listFields[dc.Ordinal]];
      }
      dtOut.Rows.Add(drData);
    }
    
    ws.Cells.ImportDataTable(dtOut, false, startRow, 0);
    startRow += dtOut.Rows.Count + 1;

    if (showResult)
    {
      ex.Save(string.Format("{0}Download.xls", templateName), FileFormatType.Excel2003, SaveType.OpenInExcel, contextCurrent.Response);
      contextCurrent.Response.End();
    }

    return ex;
  }
}